package com.carrental.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.carrental.entity.User;
import com.carrental.util.DBConnection;

//用户数据操作
public class UserDao {

	DBConnection dbConnection;
	
	//根据用户名和密码查询管理员
	public User selectByNamePwd(String name, String pwd) throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from user where username=? and password=?";
		
		ResultSet executeQuery = dbConnection.executeQuery(sql, new String[] {name, pwd});
		User user = null;
		while (executeQuery.next()) {
			user = new User();
			user.setUsername(executeQuery.getString("username"));
			user.setPassword(executeQuery.getString("password"));
			user.setPhone(executeQuery.getString("phone"));
			user.setCreateDatetime(executeQuery.getTimestamp("create_datetime"));
		}
		dbConnection.closeAll();
		return user;
	}
	
	//根据用户名查询账户
	public User selectByUsername(String name) throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from user where username=?";
		ResultSet executeQuery = dbConnection.executeQuery(sql, new String[] {name});
		User user = null;
		while (executeQuery.next()) {
			user = new User();
			user.setUsername(executeQuery.getString("username"));
			user.setPassword(executeQuery.getString("password"));
			user.setPhone(executeQuery.getString("phone"));
			user.setCreateDatetime(executeQuery.getTimestamp("create_datetime"));
		}
		dbConnection.closeAll();
		return user;
	}
	
	//查询所有用户
	public List<User> selectAll() throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from user";
		ResultSet executeQuery = dbConnection.executeQuery(sql);
		List<User> list = new ArrayList<User>();
		while (executeQuery.next()) {
			User user = new User();
			user.setUsername(executeQuery.getString("username"));
			user.setPassword(executeQuery.getString("password"));
			user.setPhone(executeQuery.getString("phone"));
			user.setCreateDatetime(executeQuery.getTimestamp("create_datetime"));
			list.add(user);
		}
		dbConnection.closeAll();
		return list;
	}
	
	//更新用户信息
	public int update(User user) throws Exception {
		dbConnection = new DBConnection();
		String sql = "update user set password = ?, phone = ? where username = ?";
		
		int res = dbConnection.executeUpdate(sql, new String[] {
				user.getPassword(), user.getPhone(), user.getUsername()
		});
		
		dbConnection.closeAll();
		return res;
		
	}
	
	//添加用户
	public int add(User user) throws Exception {
		dbConnection = new DBConnection();
		String sql = "insert into user(username,password,phone,create_datetime) values(?,?,?,?)";
		int res = dbConnection.executeUpdate(sql, new String[] {
				user.getUsername(), user.getPassword() ,user.getPhone(), user.getCreateDatetime().toString()
		});
		
		dbConnection.closeAll();
		return res;
	}
	
	//删除用户
	public int delete(String username) throws Exception {
		dbConnection = new DBConnection();
		String sql = "delete from user where username = ?";
		int res = dbConnection.executeUpdate(sql, new String[] {username});
		dbConnection.closeAll();
		return res;
	}

}
